package com.sykj.mapper;


import com.sykj.bean.Mygold;
import com.sykj.bean.bis.BisNurseBag;
import com.sykj.bean.bis.BisNurseProject;
import com.sykj.bean.bis.BisShop;
import com.sykj.bean.card.BisNurseBagDto;
import com.sykj.bean.card.BisNurseProjectDto;
import com.sykj.bean.card.BisShopDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @program: hongbao
 * @description:
 * @author: qiaoli
 * @create: 2020-08-05 16:54
 */
@Component
@Mapper
public interface GameLoadMapper {
    // 根据id 查询  活动下的奖品 及数量
    @Select("select  bnb.*, cgp.amount  from  bis_nurse_bag bnb  " +
            "join card_game_product cgp   on   bnb.bag_id=cgp.productid  " +
            "where  cgp.gameid=#{gameId,jdbcType=VARCHAR}")
    public List<BisNurseBagDto> getByGameId(String gameId);
// 更据id 查询   用户 参见活动的历史记录
@Select(
        "SELECT n.*,g.hittime from " +
         "bis_custom_user u,bis_nurse_bag n,card_user_hit g  " +
         "where u.custom_user_id=g.userid and n.bag_id=g.productid " +
         " and u.custom_user_id=#{customUserId,jdbcType=VARCHAR} and g.gameid=#{gameId,jdbcType=VARCHAR}"
)
// 更据id 查询  用户在 那个活动下中的奖品信息纪录
    public List<BisNurseBagDto> queryByid(String gameId,String customUserId);

//  更据用户id 查询  用户关注的商家店铺
@Select("select  bs.*, bc.collect_id  from  bis_shop bs  " +
        "join bis_shop_collect bc   on   bc.shop_id=bs.shop_id  " +
        "where bc.custom_user_id=#{customUserId,jdbcType=VARCHAR}")
    public List<BisShopDto>  queryShopByid(String customUserId);

//  更据 礼包id 查询  礼包下的护理项目信息
@Select(

        "select bnp.*,bbp.project_num , bbp.project_old_price, bbp.project_new_price, bnb.bag_name " +
         " from     bis_nurse_project bnp ,   bis_nurse_bag bnb,  "+
         " bis_bag_project bbp "+
        " where    bnb.bag_id=bbp.bag_id and  bnp.project_id=bbp.project_id " +
        " and    bnb.bag_id=#{bagId,jdbcType=VARCHAR} "
)
    public  List<BisNurseProjectDto> queryProjectByid(String bagId);



// 更据用户id 查询用户  已购买的礼包
    @Select(
           " SELECT bnb.*, bbr.record_id  from  "+
          "  bis_nurse_bag bnb,  "+
           "  bis_custom_user bcu,  "+
           "  bis_bag_record  bbr  "+
          "  where   bbr.bag_id=bnb.bag_id and  bbr.custom_user_id=bcu.custom_user_id   " +
          "  and    bcu.custom_user_id=#{customUserId,jdbcType=VARCHAR} "
    )
    public  List<BisNurseBagDto>  queryBagByuserid(String customUserId);


    // 更据 礼包id 和 购买礼包记录id 查询   未使用和已使用的礼包
    @Select(
           " SELECT * from   "+
                 "   (SELECT  n.bag_id,n.bag_name, "+
                 "       p.project_num, "+
                 "       np.* "+
                  "                  from bis_nurse_bag n, " +
                   "         bis_bag_project p,        "+
               "             bis_nurse_project np    "+
                "            where n.bag_id =p.bag_id and p.project_id=np.project_id and n.bag_id=#{bagId,jdbcType=VARCHAR}) aa  "+
             "  LEFT JOIN bis_project_record re on aa.bag_id=re.bag_id  "+
            "  and aa.project_id=re.project_id and re.bag_record_id=#{recordId,jdbcType=VARCHAR} "
    )
    public List<BisNurseProjectDto>  queryProjectBybagidor(String bagId,String recordId);




@Select(
        "SELECT bnp.*, bbo.order_status  " +
       "from  bis_business_order bbo, bis_nurse_project bnp  "+
  "       where         bbo.project_id=bnp.project_id   and   bbo.order_type='3'  and  " +
                "   order_status='0'  and  " +
  "  bbo.custom_user_id= #{customUserId,jdbcType=VARCHAR} "
)

public List<BisNurseProjectDto> queryProjectByuser(String customUserId);





// 查询  用户已使用的礼包
//    @Select(
//           " select bnb.* from bis_project_record bpr , bis_bag_record bbr, bis_nurse_bag bnb   "+
//           "    WHERE   "+
//           " bpr.bag_record_id=bbr.record_id  and bbr.bag_id=bnb.bag_id   and  " +
//                   " bbr.custom_user_id=#{customUserId,jdbcType=VARCHAR} "
//    )
    @Select(
          "  SELECT * from (   "+
           "         SELECT bp.bag_id,nb.soldout_time,np.* from bis_bag_project bp, bis_nurse_project np,bis_nurse_bag nb where nb.bag_id=bp.bag_id and bp.project_id=np.project_id and bp.bag_id in(   "+
            "                SELECT bag_id from bis_bag_record  where custom_user_id=#{customUserId,jdbcType=VARCHAR}   "+
          "            )   "+
         "   ) aa , bis_project_record pr where aa.bag_id=pr.bag_id and aa.project_id=pr.project_id  "+
        "    and pr.project_status=#{status,jdbcType=VARCHAR}    "
    )
    public List<BisNurseProject>   queryBagforStatus(String customUserId,String status);



@Select(
     "   SELECT * from (    "+
     "           SELECT bp.bag_id,nb.soldout_time,np.* from bis_bag_project bp, bis_nurse_project np,bis_nurse_bag nb where nb.bag_id=bp.bag_id and bp.project_id=np.project_id and bp.bag_id in(   "+
     "                   SELECT bag_id from bis_bag_record  where custom_user_id=#{customUserId,jdbcType=VARCHAR}   "+
     "           )   "+
     "   ) aa where not EXISTS(SELECT 1 from bis_project_record pr where pr.project_id=aa.project_id and pr.bag_id=aa.bag_id)  "
)
    public  List<BisNurseProject>   queryBagforidwei(String customUserId);

// 查询浏览量前十的

   @Select(
          " select * from (select bnp.* from  bis_nurse_project bnp  LEFT JOIN dian_zhan dz  on     dz.project=bnp.project_id  "+
            "       where   1=1   order by  dz.hits  desc   limit  0, 10 )  aa     "
   )
    public  List<BisNurseProject>  queryDianzhanforProject();




   @Select(
         "   select bnp.*    "+
         "   from  bis_nurse_project bnp  "+
         "  LEFT JOIN search s  on         "+
        "   s.project_id=bnp.project_id    "+
        "   where   s.s_type='1' and s.user_id=#{customUserId,jdbcType=VARCHAR}"+
         "  order by "+
        "   s.s_num  desc   limit  0, 5  "
   )
   public List<BisNurseProject> querySearchByuserid(String customUserId);


   @Select(
          " SELECT * from  bis_nurse_project bnp  where "+
          " bnp.project_name like CONCAT('%',#{projectName,jdbcType=VARCHAR},'%')   "
   )
   public List<BisNurseProject> queryNameByproject(String projectName);




}
